########################################################################
#  Pyfm - personal finance data-base
#  Copyright (C) 2007,8 Ehud Ben-Reuven
#  udi@benreuven.com
#
# This program is free software; you can redistribute it and/or
# modify it under the terms of the GNU General Public License
# as published by the Free Software Foundation version 2.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
########################################################################
"""Convert BLL IL Portfolio Activity XLS file to flat lines.
"""
from csv2flt import *
from obj.flat import stagvalue,ptagvalue,pacctid,psecid
import re
from xls2csv2 import xls2csv2
from obj import dt
from lib import dirs
import logging

def acctidre(name):
	return '(?P<%s>[\d\-\/]+)'%name

def secid(name):
	return '(?P<%s>\d{6,7})'%name

htf_list=[
#version 0
([
	['', '', '', '', '', '', '', '', '', '', ''],
	['\xfa\xf0\xe5\xf2\xe5\xfa \xe4\xf1\xe8\xe5\xf8\xe9\xe5\xfa', '', '', '', '', '', '', '', '', '', ''], # In Hebrew: Historical transactions
	['\xfa\xe0\xf8\xe9\xea: '+eurdre('DTASOF','/'), '', '', '', '', '', '', '', '', '', ''],
	['', '', '', '', '', '', '', '', '', '', ''],
	['', '', '', '', '', '', '', '', '', '', ''],
	['\xee\xf1\xf4\xf8 \xf0\xe9\xe9\xf8', # Asset #
	 '\xf9\xed \xe4\xf0\xe9\xf8', # Asset Name
	 '\xf4\xf2\xe5\xec\xe4', # operation
	 '\xfa\xe0\xf8\xe9\xea \xfa\xf9\xec\xe5\xed', # date of payment
	 '\xeb\xee\xe5\xfa \xee\xf7\xe5\xf8\xe9\xfa', #original amount
	 '\xeb\xee\xe5\xfa \xe1\xe9\xf6\xe5\xf2', # actual amount
	 '\xf9\xf2\xf8 \xe1\xe9\xf6\xe5\xf2', # rate
	 '\xfa\xee\xe5\xf8\xe4', # received
	 '\xf2\xee\xec\xe5\xfa', # expense
	 '\xee\xe8\xe1\xf2', # currency
	 '\xf1\xe8\xe8\xe5\xf1'] # status
],
[secid('SECID')+'.0',
      strre('SECNAME'),
      strre('TRNTYPE'),
      eurdre('DTSETTLE','/',year_len=2)+'( 00:00:00)?',
      floatre('ORIGUNITS'),
      floatre('UNITS'),
      floatre('UNITPRICE'),
      floatre('net_total'),
      floatre('FEES'),
      strre('CURRENCY'),
      strre('STATUS')],
None),
#version1
([
	['', '', '', '', '', '', '', '', '', ''],
	['\xfa\xf0\xe5\xf2\xe5\xfa \xe4\xf1\xe8\xe5\xf8\xe9\xe5\xfa', '', '', '', '', '', '', '', '', ''], # In Hebrew: Historical transactions
	['('+'\xfa\xe0\xf8\xe9\xea: '+eurdre('DTASOF','/')+')?', '', '', '', '', '', '', '', '('+'\xfa\xe0\xf8\xe9\xea: '+eurdre('DTASOF','/')+')?', ''],
	['', '', '', '', '', '', '', '', '', ''],
	['', '', '', '', '', '', '', '', '', ''],
	['\xee\xf1\xf4\xf8 \xf0\xe9\xe9\xf8', # Asset #
	 '\xf9\xed \xe4\xf0\xe9\xf8', # Asset Name
	 '\xf4\xf2\xe5\xec\xe4', # operation
	 '\xfa\xe0\xf8\xe9\xea \xf2\xf8\xea', # value date
	 '\xeb\xee\xe5\xfa \xee\xf7\xe5\xf8\xe9\xfa', #original amount
	 '\xeb\xee\xe5\xfa \xe1\xe9\xf6\xe5\xf2', # actual amount
	 '\xf9\xf2\xf8 \xe1\xe9\xf6\xe5\xf2', # rate
	 '\xfa\xee\xe5\xf8\xe4 \xf0\xe8\xe5', # received net
	 '\xf2\xee\xec\xe5\xfa', # expense
	 '\xf1\xe8\xe8\xe5\xf1'] # status
],
[secid('SECID')+'.0',
      strre('SECNAME'),
      strre('TRNTYPE'),
      eurdre('DTSETTLE','/',year_len=2)+'( 00:00:00)?',
      floatre('ORIGUNITS'),
      floatre('UNITS'),
      floatre('UNITPRICE'),
      floatre('net_total'),
      floatre('FEES'),
      strre('STATUS')],
None),
#version2
([
	['', '', '', '', '', '', '', '', '', '', ''],
	['\xfa\xf0\xe5\xf2\xe5\xfa \xe4\xf1\xe8\xe5\xf8\xe9\xe5\xfa', '', '', '', '', '', '', '', '', '', ''], # In Hebrew: Historical transactions
	['('+'\xfa\xe0\xf8\xe9\xea: '+eurdre('DTASOF','/')+')?', '', '', '', '', '', '', '', '', '', ''],
	['', '', '', '', '', '', '', '', '', '', ''],
	['', '', '', '', '', '', '', '', '', '', ''],
	['\xee\xf1\xf4\xf8 \xf0\xe9\xe9\xf8', # Asset #
	 '\xf9\xed \xe4\xf0\xe9\xf8', # Asset Name
	 '\xf4\xf2\xe5\xec\xe4', # operation
	 '\xfa\xe0\xf8\xe9\xea \xf2\xf8\xea', # value date
	 '\xeb\xee\xe5\xfa \xee\xf7\xe5\xf8\xe9\xfa', #original amount
	 '\xeb\xee\xe5\xfa \xe1\xe9\xf6\xe5\xf2', # actual amount
	 '\xf9\xf2\xf8 \xe1\xe9\xf6\xe5\xf2', # rate
	 '\xfa\xee\xe5\xf8\xe4 \xf0\xe8\xe5', # received net
	 '\xf2\xee\xec\xe5\xfa', # expense
	 '\xee\xe8\xe1\xf2', # currency
	 '\xf1\xe8\xe8\xe5\xf1'] # status
],
[secid('SECID')+'.0',
      strre('SECNAME'),
      strre('TRNTYPE'),
      eurdre('DTSETTLE','/',year_len=2)+'( 00:00:00)?',
      floatre('ORIGUNITS'),
      floatre('UNITS'),
      floatre('UNITPRICE'),
      floatre('net_total'),
      floatre('FEES'),
      strre('CURRENCY'),
      strre('STATUS')],
None)
]

def pre(fname,csvfile):
	xls2csv2(fname,csvfile,'hebrew')
	
def flat(csvfile,fout,linetxt):
	name=dirs.name(str(csvfile))
	l= re.match('(?P<ACCTGRP>[A-Z][A-Za-z0-9]*)-\d{6}(-|\Z)',name)
	if not l: raise Exception('Bad file name '+name)
	acctgrp=l.groupdict()['ACCTGRP']
	
	h,t,f,version=readcsvtable_multiformat(csvfile,htf_list)

	l=h[2]
	dtasof = str(dt.expandyear(int(l['DTASOF_year'])))+l['DTASOF_month']+l['DTASOF_day']
	accttype='INVESTMENT'

	for l in t:
		dtsettle = str(dt.expandyear(int(l['DTSETTLE_year'])))+l['DTSETTLE_month']+l['DTSETTLE_day']
		if dtsettle>dtasof:
			raise Exception('DTSETTLE=%s after DTASOF=%s of the entire document'%(dtsettle,dtasof))
		units=float(l['UNITS'])
		origunits=float(l['ORIGUNITS'])
		if units:
			unitprice=float(l['UNITPRICE'])/100
			assert unitprice>0
		else:
			unitprice=0
			assert float(l['UNITPRICE'])==0

		taxes=0
		interest=0
		cost=0
		total=0
		net_total=0
		fees=0

		fees=float(l['FEES'])
		if version==1 or version==2: fees=-fees
		wrong_fee_sign= fees<0
		if wrong_fee_sign:
			logging.warning('%s','Wrong fees sign %f'%fees)
			fees=-fees
			

		try:
			trntype={'\xf7\xf0\xe9\xe4':'BUY',
				 '\xee\xeb\xe9\xf8\xe4':'SELL',
				 '\xe4\xe6\xee\xf0\xe4':'ORDER',
				 '\xf8\xe9\xe1\xe9\xfa':'INT',
				 '\xe2\xe1\xe9\xfa \xee\xf1':'TAX',
				 '\xe4\xe7\xe6\xf8 \xee\xf1 \xf8\xe5\xe5\xe7 \xe4\xe5\xef':'TAXRETURN',
				 '\xe3\xe9\xe1\xe9\xe3\xf0\xe3':'DIV',
				 }[l['TRNTYPE']]
		except:
			raise Exception('Unknown transaction type %s'%l['TRNTYPE'])
		if trntype=='ORDER':
			if units>0:
				trntype='BUY'
			else:
				trntype='SELL'
		price=float(l['net_total'])
		if trntype=='BUY':
			if price>=0:
				if wrong_fee_sign:
					logging.warning('%s','Wrong BUY sign')
				else:
					print 'Wrong BUY sign'
				price=-price
		elif trntype=='SELL':
			if price<=0:
				if wrong_fee_sign:
					logging.warning('%s','Wrong SELL sign')
				else:
					print 'Wrong SELL sign'
				price=-price
		elif trntype=='INT':
			if price<=0:
				if wrong_fee_sign:
					logging.warning('%s','Wrong INT sign')
				else:
					print 'Wrong INT sign'
				price=-price
		
		if version==1 or version==2:
			total=price
			net_total=total+fees
		else:
			net_total=price
			total=net_total-fees
			
		if version==1:
			currency='ILS'
		elif version==0 or version==2:
			try:
				currency={'\xf9"\xe7':'ILS'}[l['CURRENCY']]
			except:
				raise Exception('Unknown currency %s'%l['CURRENCY'])
			
		if l['STATUS'] not in ['\xee\xec\xe0',  # complete
							   '\xe2\xee\xf8 \xe1\xe9\xf6\xe5\xf2', #end operation
							   '\xe7\xec\xf7\xe9', # partial
							   '-']:
			raise Exception('Unknown status %s'%l['STATUS'])
		
		
		if trntype in ['BUY', 'SELL']:
			if trntype == 'BUY' and units<=0: print 'Wrong units sign on buy'
			if trntype == 'SELL' and units>=0: print 'Wrong units sign on sell'
			if units!=origunits:
				logging.info('%s','Units differ from original units '+l['UNITS']+' '+l['ORIGUNITS'])
			assert units
			assert unitprice
			cost=-net_total
			if trntype=='BUY':
				assert cost>0
			else:
				assert cost<0
		elif trntype == 'INT' or trntype == 'DIV':
			assert not units
			assert not origunits
			assert not unitprice
			interest=net_total
			assert interest>0
		elif trntype=='TAX':
			assert not units
			assert not unitprice
			assert not net_total
			assert not fees
			taxes=-origunits
			assert taxes>0
			total=-taxes
		elif trntype=='TAXRETURN':
			trntype='TAX'
			assert(units==0)
			assert(unitprice==0)
			assert(net_total==0)
			assert(fees==0)
			taxes=origunits
			assert taxes<0
			total=-taxes
		else:
			raise Exception('Bug - unknown trntype')
		
		diff=-units*unitprice+interest-fees-taxes-total
		if abs(diff)>0.5:
			if (version==1 or version==2) and diff>0 and fees and not taxes:
				if trntype=='SELL':
					logging.warning('%s',"%s price missmatch taking it as TAX on capital-gain %f=%.1f%%"%(stagvalue('DTSETTLE',dtsettle),diff,100.*diff/(-units*unitprice)))
					taxes=diff
				elif trntype=='INT':
					logging.warning('%s',"%s price missmatch taking it as TAX on interest %f=%.1f%%"%(stagvalue('DTSETTLE',dtsettle),diff,100.*diff/interest))
					taxes=diff
				else:
					logging.error('%s',"%s price missmatch %f"%(stagvalue('DTSETTLE',dtsettle),diff))
			else:
				logging.error('%s',"%s price missmatch %f"%(stagvalue('DTSETTLE',dtsettle),diff))
				
		print >>fout,stagvalue('DTSETTLE',dtsettle),
		print >>fout,stagvalue('ACCTTYPE','INVESTMENT'),stagvalue('ACCTGRP',acctgrp),
		print >>fout,stagvalue('SECID','TASE='+l['SECID']),stagvalue('SECNAME',l['SECNAME']),
		print >>fout,stagvalue('TRNTYPE',trntype),
		if units: print >>fout,stagvalue('UNITS',units),
		if unitprice: print >>fout,stagvalue('UNITPRICE',unitprice),
		if interest: print >>fout,stagvalue('INTEREST',interest),
		if total: print >>fout,stagvalue('TOTAL',total),
		if fees: print >>fout,stagvalue('FEES',fees),
		if taxes: print >>fout,stagvalue('TAXES',taxes),
		print >>fout,stagvalue('CURRENCY',currency),
		# Assume that the asset came from an account that has an identifier made from the Account group and investment ('i')
		acctid=acctgrp+'_investment_'+currency
		print >>fout,stagvalue('ACCTID',acctid),
		print >>fout,stagvalue('DTEND',dtasof),stagvalue('FULL','TRUE'),
		# Assume that the account in which the holding is being kept has a default currency which is the same as the currency used for the transaction.
		print >>fout,stagvalue('CURDEF',currency),
		print >>fout,linetxt

help="""Convert BLL portfolio activity XLS file to flat lines.
The XLS files should be located at $PYFM_DATA/imports/BLL_IL/portfolio/activity/
the file is first converted to CSV file in $PYFM_DATA/temp
and then to flat file at  $PYFM_DATA/flt/BLL_IL/portfolio/activity/
"""
def path(line):
	return ['BLL_IL','portfolio','activity']

ext='*.xls'
